package SiTuQingYou.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import SiTuQingYou.util.DBHelper;
import SiTuQingYou.util.Pager;
import SiTuQingYou.vo.City;
import SiTuQingYou.vo.Scene;
import SiTuQingYou.vo.SceneClass;		

public class SceneDao
{
	private  SceneDao() {}
	private static  SceneDao scened= new  SceneDao();
	public static  SceneDao getscened()
	{
		return scened;
	}
	public String getTopSceneId()
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn=DBHelper.getDB().getConn();
			String sql="select id from scene order by hotscore desc LIMIT 0,4";
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			String str="";
			while(rs.next())
			{
				str=str+","+rs.getInt("id");
			}
			return str;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	//全部景点
	public ArrayList<Scene> allscenes(){   
		
		    DBHelper dbhelper = DBHelper.getDB();
		    Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				conn = dbhelper.getConn();
				String sql = "select * from scene,city,sceneclass  where cityid=city.id and themeid=sceneclass.id and fakedelete=0 order by  scene.id desc    ";
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				ArrayList<Scene> allscene = new ArrayList<Scene>();
				while(rs.next()) {
					Scene scene = new Scene();
					scene.setId(rs.getInt("id"));
					City city = new City();
					city.setCityname(rs.getString("cityname"));
					scene.setCity(city);
					SceneClass sceneclass = new SceneClass();
					sceneclass.setClassname(rs.getString("classname"));
					scene.setTheme(sceneclass);
					scene.setHotscore(rs.getInt("hotscore"));
					scene.setHot(rs.getBoolean("hot"));
					scene.setIntro(rs.getString("intro"));
					scene.setScenename(rs.getString("scenename"));
					allscene.add(scene);
				}
				
			  return allscene;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				dbhelper.close(rs, ps, conn);
			}
		return null;
		
	}
	
	
	//删一个
	public void deleteone (int id) {
		
		    DBHelper dbhelper = DBHelper.getDB();
		    Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null; 
			
			try {
				conn = dbhelper.getConn();
				String sql = "delete from scene where id=?";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, id);
				ps.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				dbhelper.close(rs, ps, conn);
			}
	}
	  //修改
	public void modify(int cityid,String scenename,int themeid,String intro,Boolean hot,int id,int hotscore) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null; 
		
		try {
			conn = DBHelper.getDB().getConn();
			String  sql = "update  scene  set  cityid=?,scenename=?,themeid=?,intro=?,hot=?,hotscore=? where  id=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1,cityid);
			ps.setString(2, scenename);
			ps.setInt(3, themeid);
			ps.setString(4, intro);
			ps.setBoolean(5, hot);
			ps.setInt(6, hotscore);
			ps.setInt(7, id);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getDB().close(rs, ps, conn);
		}
	}
	//通过主题id查景点
	    public ArrayList<Scene> getAllSceneByThemeId(int id,Pager pager)
	    {
	    	pager.setPageSize(Integer.parseInt("6"));
			pager.setDataCount(this.getSceneCountByThemeId(id));
	    	Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null; 
			try {
				conn = DBHelper.getDB().getConn();
				String sql = "select * from scene where themeid=? limit ?,?";
				ps = conn.prepareStatement(sql);
				ps.setInt(1,id);
				ps.setInt(2, pager.getFirstRow());
				ps.setInt(3, pager.getPageSize());
				rs = ps.executeQuery();
				ArrayList<Scene> allscene = new ArrayList<Scene>();
				while(rs.next())
				{
					Scene scene = new Scene();
					scene.setId(rs.getInt("id"));
					City city = new City();
					city.setId(rs.getInt("cityid"));
					scene.setCity(city);
					scene.setHot(rs.getBoolean("hot"));
					scene.setIntro(rs.getString("intro"));
					scene.setScenename(rs.getString("scenename"));
					allscene.add(scene);
				}
				return allscene;
				
			} catch (Exception e) {
				e.printStackTrace();
			}
			finally
			{
				DBHelper.getDB().close(rs, ps, conn);
			}
			return null;
			
	    }
	
	  //模糊查询景点
		 public ArrayList<Scene> checkscene(String check){
			 DBHelper dbhelper = DBHelper.getDB();
			  Connection conn = null;
				PreparedStatement ps = null;
				ResultSet rs = null; 
				 
				try {
					conn = dbhelper.getConn();
					String sql = "select * from scene,city,sceneclass where cityid=city.id and themeid=sceneclass.id and scenename like '%"+check+"%'";
					ps = conn.prepareStatement(sql);
					rs = ps.executeQuery();
					ArrayList<Scene> allscene = new ArrayList<Scene>();
					while(rs.next()) {
						Scene scene = new Scene();
						scene.setId(rs.getInt("id"));
						City city = new City();
						city.setCityname(rs.getString("cityname"));
						scene.setCity(city);
						SceneClass sceneclass = new SceneClass();
						sceneclass.setClassname(rs.getString("classname"));
						scene.setTheme(sceneclass);
						scene.setHot(rs.getBoolean("hot"));
						scene.setIntro(rs.getString("intro"));
						scene.setScenename(rs.getString("scenename"));
						allscene.add(scene);
					}
					
				  return allscene;
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				finally {
					dbhelper.close(rs, ps, conn);
				}
				return null;
		 }
	  
	 //鏍规嵁 ID 鍒犻櫎   鏅偣
	 public void deleteall(String cid[]) {
		 
		 String s="";
		 for (String id:cid) {
			 s = s+","+id;
		 }
		 s = s.substring(1);
			DBHelper dbhelper = DBHelper.getDB();
			  Connection conn = null;
				PreparedStatement ps = null;
				ResultSet rs = null; 
				
				try {
					conn = dbhelper.getConn();
					String sql = "delete from scene where id in ("+s+")";
					ps = conn.prepareStatement(sql);
					ps.executeUpdate();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				finally {
					dbhelper.close(rs, ps, conn);
				}
	 }
	
	 
	public Scene getSceneById(int id)     
	{
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			conn=DBHelper.getDB().getConn();
			String sql="select * from scene,sceneclass,city,scenephoto where scene.id=? and themeid=sceneclass.id and cityid=city.id and scene.fakedelete=0";
			ps=conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs=ps.executeQuery();
			rs.next();
			Scene scene=new Scene();
			//ScenePhoto scenephoto = new ScenePhoto();
			//scenephoto.setScenepic(rs.getString("scenepic"));
			//scene.setScenephoto(scenephoto);
			City city=new City();
			city.setId(rs.getInt("cityid"));
			city.setCityname(rs.getString("cityname"));
			scene.setCity(city);
			scene.setId(rs.getInt("id"));
			scene.setHot(rs.getBoolean("hot"));
			scene.setScenename(rs.getString("scenename"));
			scene.setIntro(rs.getString("intro"));
			scene.setHotscore(rs.getInt("hotscore"));
			SceneClass theme=new SceneClass();
			theme.setClassname(rs.getString("classname"));
			theme.setId(rs.getInt("themeid"));
			scene.setTheme(theme);
			return scene;
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally
		{
			DBHelper.getDB().close(rs, ps, conn);
		}
		return null;
	}
	
	public ArrayList<Scene> cityscene(int id){
		DBHelper dbhelper = DBHelper.getDB();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = dbhelper.getConn();
			String sql = "select * from scene,city,sceneclass where cityid=city.id and themeid=sceneclass.id and cityid=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			ArrayList<Scene> cityscene = new ArrayList<Scene>();
			while(rs.next())
			{
				Scene scene = new Scene();
				scene.setId(rs.getInt("id"));
				City city = new City();
				city.setId(rs.getInt("cityid"));
				scene.setCity(city);
				SceneClass sceneclass = new SceneClass();
				sceneclass.setClassname(rs.getString("classname"));
				scene.setTheme(sceneclass);
				scene.setHot(rs.getBoolean("hot"));
				scene.setIntro(rs.getString("intro"));
				scene.setScenename(rs.getString("scenename"));
				cityscene.add(scene);
			}
			return cityscene;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		finally {
			dbhelper.close(rs, ps, conn);
		}
		return null;	
	}
	
	public ArrayList<Scene> allscene(Pager pager){   
		pager.setPageSize(Integer.parseInt("4"));
		pager.setDataCount(this.getscenecount());
		DBHelper dbhelper = DBHelper.getDB();
		  Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				conn = dbhelper.getConn();
				String sql = "select * from scene,city,sceneclass  where cityid=city.id and themeid=sceneclass.id and fakedelete=0 order by hotscore desc limit ?,?";
				ps = conn.prepareStatement(sql);
				//System.out.println(pager.getPageSize());
				ps.setInt(1,pager.getFirstRow());
				ps.setInt(2,pager.getPageSize());
				rs = ps.executeQuery();
				ArrayList<Scene> allscene = new ArrayList<Scene>();
				
				while(rs.next()) {
					
					Scene scene = new Scene();
					scene.setId(rs.getInt("id"));
					City city = new City();
					city.setCityname(rs.getString("cityname"));
					scene.setCity(city);
					SceneClass sceneclass = new SceneClass();
					sceneclass.setClassname(rs.getString("classname"));
					scene.setTheme(sceneclass);
					scene.setHotscore(rs.getInt("hotscore"));
					scene.setHot(rs.getBoolean("hot"));
					scene.setIntro(rs.getString("intro"));
					scene.setScenename(rs.getString("scenename"));
					allscene.add(scene);
				}
				
			  return allscene;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				dbhelper.close(rs, ps, conn);
			}
		return null;
		
	}
	
	public int getscenecount()
	{
		DBHelper dbhelper = DBHelper.getDB();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int sceneNum=0;
		try {
			conn = dbhelper.getConn();
			String sql = "select count(id) as sceneNum from scene where fakedelete=0";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs.next()) {
				sceneNum = rs.getInt(1);
			}	
		} 
		catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		finally {
			dbhelper.close(rs, ps, conn);
		}
		return sceneNum;
	}
	public int getSceneCountByThemeId(int id)
	{
		DBHelper dbhelper = DBHelper.getDB();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int sceneNum=0;
		try {
			conn = dbhelper.getConn();
			String sql = "select count(id) as sceneNum from scene where fakedelete=0 and themeid=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if(rs.next()) {
				sceneNum = rs.getInt(1);
			}	
		} 
		catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		finally {
			dbhelper.close(rs, ps, conn);
		}
		return sceneNum;
	}
	
	//娣诲姞鏅偣
	public void newscene(int cityid,String scenename,int themeid,boolean hot,String intro,int hots) { 
		DBHelper dbhelper = DBHelper.getDB();
		  Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null; 
			
			try {
				conn = dbhelper.getConn();
				String sql = "insert into scene(cityid,scenename,themeid,hot,intro,hotscore)values(?,?,?,?,?,?)";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, cityid);
				ps.setString(2, scenename);
				ps.setInt(3, themeid);
				ps.setBoolean(4, hot);
				ps.setString(5, intro);
				ps.setInt(6, hots);
				ps.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				dbhelper.close(rs, ps, conn);
			}
			
			
	}
	
	//查询所有的景点总数
	public int scenednum () {
		DBHelper dbhelper = DBHelper.getDB();
		  Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null; 
			
			try {
				 conn = dbhelper.getConn();
				 
				String sql ="select COUNT(*) as count from scene ";
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				 rs.next();
				 int a = rs.getInt("count");
				 return a;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				dbhelper.close(rs, ps, conn);
			}
			return 0 ;
			
	}
	
}
